cd "C:\Users\pgi1\Dropbox\Coal 2021 _ Joe Peter\Bank Regressions\data"




/////////////////////////////////////////
// Combine bank-loan level dataset with assets data from bank regulatory
use packageid  ultimateparentid dealactivedate prod_tons  using lenders_borrowers_dataset, clear
keep if !mi( prod_tons )
drop prod_tons 

count
joinby ultimateparentid  using bank-assets, unm(both)
tab _m
keep if _m==3
drop _m

gen date_assets=dofm(date)
format date_assets %td

keep if  date_assets < dealactivedate


bysort packageid ultimateparentid : egen max_date_assets=max(date_assets)
format max_date_assets %td

keep if date_assets==max_date_assets
count

keep if dealactivedate-date_assets<=365

keep packageid ultimateparentid   total_assets has_deposits asset_growth_3

compress
bysort packageid ultimateparentid : keep if _n==1

save matched-packageid-data, replace
count



////////////////////////////////////////////////////////////////////////
// Create counts of total and related loans in preceeding three years

use lenders_borrowers_dataset, clear

collapse (sum) deal_amount_total=deal_amount_converted (count) loans_total=deal_amount_converted, by(ultimateparentid year)
drop if year==2021

tsset ultimateparentid year

foreach var of varlist deal_amount_total loans_total  {
	gen `var'_lag3=(L.`var'+L2.`var'+L3.`var')/3
}


gen loans_total_lag3_ln=ln(loans_total_lag3+1)
keep ultimateparentid year loans_total_lag3_ln
bysort ultimateparentid year: keep if _n==1
save total_loans_by_year_lagged, replace




use lenders_borrowers_dataset, clear
rename primarysiccode sic
keep if ((sic>= 1220 & sic<=1221) | (sic>=3310 & sic<=3329) | (sic>=4900 & sic<=4939))

collapse (sum) deal_amount_related=deal_amount_converted (count) loans_related=deal_amount_converted, by(ultimateparentid year)
drop if year==2021

tsset ultimateparentid year

foreach var of varlist deal_amount_related loans_related  {
	gen `var'_lag3=(L.`var'+L2.`var'+L3.`var')/3
}
gen loans_related_lag3_ln=ln(loans_related_lag3+1)
keep ultimateparentid year loans_related_lag3_ln

bysort ultimateparentid year: keep if _n==1
save related_loans_by_year_lagged, replace




////////////////////////////////////////////////////////////////////////
// Now make match dataset at the bank-deal level 
use lenders_borrowers_dataset, clear
bysort packageid ultimateparentid : keep if _n==1
keep if !mi( prod_tons )

joinby packageid ultimateparentid using matched-packageid-data, unm(master)
tab _m
drop _m

// add total loans in preceeding theree years
joinby ultimateparentid year using total_loans_by_year_lagged, unm(master)
tab _m
drop _m


// add related loans in preceeding theree years
joinby ultimateparentid year using related_loans_by_year_lagged, unm(master)
tab _m
drop _m

// Add in mtr news (file done in 5-load-nexis)
joinby ultimateparentid year using article-bank-year, unm(master)
tab _m
drop _m
replace relevant_article=0 if mi(relevant_article)
label var relevant_article "News Articles"

// Add in mtr deposits (file done in 6-branch-location)
joinby ultimateparentid year using mtrdepositshare, unm(master)
tab _m
drop _m
replace mtrdepositshare=0 if mi(mtrdepositshare)
label var mtrdepositshare "Share of Deposits in MTR States"




compress
bysort packageid ultimateparentid : keep if _n==1
save matched_dataset_deal, replace




